{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Help Desk - Easy\n",
    "\n",
    "## Scenario\n",
    "A software company has been successful in selling its products to a number of customer organisations, and there is now a high demand for technical support. There is already a system in place for logging support calls taken over the telephone and assigning them to engineers, but it is based on a series of spreadsheets. With the growing volume of data, using the spreadsheet system is becoming slow, and there is a significant risk that errors will be made.\n",
    "\n",
    "![rel](https://sqlzoo.net/w/images/3/38/Helpdesk.png)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "-- \u001b[1mAttaching packages\u001b[22m --------------------------------------- tidyverse 1.3.0 --\n",
      "\n",
      "\u001b[32mv\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.0     \u001b[32mv\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32mv\u001b[39m \u001b[34mtibble \u001b[39m 3.0.1     \u001b[32mv\u001b[39m \u001b[34mdplyr  \u001b[39m 0.8.5\n",
      "\u001b[32mv\u001b[39m \u001b[34mtidyr  \u001b[39m 1.0.2     \u001b[32mv\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32mv\u001b[39m \u001b[34mreadr  \u001b[39m 1.3.1     \u001b[32mv\u001b[39m \u001b[34mforcats\u001b[39m 0.5.0\n",
      "\n",
      "-- \u001b[1mConflicts\u001b[22m ------------------------------------------ tidyverse_conflicts() --\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31mx\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Password? ·········\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1.\n",
    "There are three issues that include the words \"index\" and \"Oracle\". Find the call_date for each of them\n",
    "\n",
    "```\n",
    "+---------------------+----------+\n",
    "| call_date           | call_ref |\n",
    "+---------------------+----------+\n",
    "| 2017-08-12 16:00:00 |     1308 |\n",
    "| 2017-08-16 14:54:00 |     1697 |\n",
    "| 2017-08-16 19:12:00 |     1731 |\n",
    "+---------------------+----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "shift <- dbReadTable(con, 'Shift')\n",
    "staff <- dbReadTable(con, 'Staff')\n",
    "issue <- dbReadTable(con, 'Issue')\n",
    "shift_type <- dbReadTable(con, 'Shift_type')\n",
    "level <- dbReadTable(con, 'Level')\n",
    "customer <- dbReadTable(con, 'Customer')\n",
    "caller <- dbReadTable(con, 'Caller')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Call_date</th><th scope=col>Call_ref</th></tr>\n",
       "\t<tr><th scope=col>&lt;dttm&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2017-08-12 16:00:00</td><td>1308</td></tr>\n",
       "\t<tr><td>2017-08-16 14:54:00</td><td>1697</td></tr>\n",
       "\t<tr><td>2017-08-16 19:12:00</td><td>1731</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 2\n",
       "\\begin{tabular}{ll}\n",
       " Call\\_date & Call\\_ref\\\\\n",
       " <dttm> & <int>\\\\\n",
       "\\hline\n",
       "\t 2017-08-12 16:00:00 & 1308\\\\\n",
       "\t 2017-08-16 14:54:00 & 1697\\\\\n",
       "\t 2017-08-16 19:12:00 & 1731\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 2\n",
       "\n",
       "| Call_date &lt;dttm&gt; | Call_ref &lt;int&gt; |\n",
       "|---|---|\n",
       "| 2017-08-12 16:00:00 | 1308 |\n",
       "| 2017-08-16 14:54:00 | 1697 |\n",
       "| 2017-08-16 19:12:00 | 1731 |\n",
       "\n"
      ],
      "text/plain": [
       "  Call_date           Call_ref\n",
       "1 2017-08-12 16:00:00 1308    \n",
       "2 2017-08-16 14:54:00 1697    \n",
       "3 2017-08-16 19:12:00 1731    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "issue %>%\n",
    "    filter(str_detect(Detail, 'index') & \n",
    "           str_detect(Detail, 'Oracle')) %>%\n",
    "    select(Call_date, Call_ref)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.\n",
    "Samantha Hall made three calls on 2017-08-14. Show the date and time for each\n",
    "\n",
    "```\n",
    "+---------------------+------------+-----------+\n",
    "| call_date           | first_name | last_name |\n",
    "+---------------------+------------+-----------+\n",
    "| 2017-08-14 10:10:00 | Samantha   | Hall      |\n",
    "| 2017-08-14 10:49:00 | Samantha   | Hall      |\n",
    "| 2017-08-14 18:18:00 | Samantha   | Hall      |\n",
    "+---------------------+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 3 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Call_date</th><th scope=col>First_name</th><th scope=col>Last_name</th></tr>\n",
       "\t<tr><th scope=col>&lt;dttm&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2017-08-14 10:10:00</td><td>Samantha</td><td>Hall</td></tr>\n",
       "\t<tr><td>2017-08-14 10:49:00</td><td>Samantha</td><td>Hall</td></tr>\n",
       "\t<tr><td>2017-08-14 18:18:00</td><td>Samantha</td><td>Hall</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 3 × 3\n",
       "\\begin{tabular}{lll}\n",
       " Call\\_date & First\\_name & Last\\_name\\\\\n",
       " <dttm> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 2017-08-14 10:10:00 & Samantha & Hall\\\\\n",
       "\t 2017-08-14 10:49:00 & Samantha & Hall\\\\\n",
       "\t 2017-08-14 18:18:00 & Samantha & Hall\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 3 × 3\n",
       "\n",
       "| Call_date &lt;dttm&gt; | First_name &lt;chr&gt; | Last_name &lt;chr&gt; |\n",
       "|---|---|---|\n",
       "| 2017-08-14 10:10:00 | Samantha | Hall |\n",
       "| 2017-08-14 10:49:00 | Samantha | Hall |\n",
       "| 2017-08-14 18:18:00 | Samantha | Hall |\n",
       "\n"
      ],
      "text/plain": [
       "  Call_date           First_name Last_name\n",
       "1 2017-08-14 10:10:00 Samantha   Hall     \n",
       "2 2017-08-14 10:49:00 Samantha   Hall     \n",
       "3 2017-08-14 18:18:00 Samantha   Hall     "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "issue %>% \n",
    "    inner_join(caller, by=c(Caller_id=\"Caller_id\")) %>%\n",
    "    filter(First_name=='Samantha' &\n",
    "           Last_name=='Hall' &\n",
    "           as.Date(Call_date)==as.Date('2017-08-14')) %>%\n",
    "    select(Call_date, First_name, Last_name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3.\n",
    "There are 500 calls in the system (roughly). Write a query that shows the number that have each status.\n",
    "\n",
    "```\n",
    "+--------+--------+\n",
    "| status | Volume |\n",
    "+--------+--------+\n",
    "| Closed |    486 |\n",
    "| Open   |     10 |\n",
    "+--------+--------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A tibble: 2 × 2</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Status</th><th scope=col>volume</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Closed</td><td>486</td></tr>\n",
       "\t<tr><td>Open  </td><td> 10</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 2 × 2\n",
       "\\begin{tabular}{ll}\n",
       " Status & volume\\\\\n",
       " <chr> & <int>\\\\\n",
       "\\hline\n",
       "\t Closed & 486\\\\\n",
       "\t Open   &  10\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 2 × 2\n",
       "\n",
       "| Status &lt;chr&gt; | volume &lt;int&gt; |\n",
       "|---|---|\n",
       "| Closed | 486 |\n",
       "| Open   |  10 |\n",
       "\n"
      ],
      "text/plain": [
       "  Status volume\n",
       "1 Closed 486   \n",
       "2 Open    10   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "issue %>%\n",
    "    group_by(Status) %>%\n",
    "    summarise(volume=n())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4.\n",
    "Calls are not normally assigned to a manager but it does happen. How many calls have been assigned to staff who are at Manager Level?\n",
    "\n",
    "```\n",
    "+------+\n",
    "| mlcc |\n",
    "+------+\n",
    "|   51 |\n",
    "+------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 1 × 1</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>mlcc</th></tr>\n",
       "\t<tr><th scope=col>&lt;int&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>51</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 1 × 1\n",
       "\\begin{tabular}{l}\n",
       " mlcc\\\\\n",
       " <int>\\\\\n",
       "\\hline\n",
       "\t 51\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 1 × 1\n",
       "\n",
       "| mlcc &lt;int&gt; |\n",
       "|---|\n",
       "| 51 |\n",
       "\n"
      ],
      "text/plain": [
       "  mlcc\n",
       "1 51  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "issue %>% \n",
    "    inner_join(staff, by=c(Assigned_to=\"Staff_code\")) %>%\n",
    "    inner_join(level, by=c(Level_code=\"Level_code\")) %>%\n",
    "    filter(Manager=='Y') %>%\n",
    "    tally %>%\n",
    "    rename(mlcc='n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5.\n",
    "Show the manager for each shift. Your output should include the shift date and type; also the first and last name of the manager.\n",
    "\n",
    "```\n",
    "+------------+------------+------------+-----------+\n",
    "| Shift_date | Shift_type | first_name | last_name |\n",
    "+------------+------------+------------+-----------+\n",
    "| 2017-08-12 | Early      | Logan      | Butler    |\n",
    "| 2017-08-12 | Late       | Ava        | Ellis     |\n",
    "| 2017-08-13 | Early      | Ava        | Ellis     |\n",
    "| 2017-08-13 | Late       | Ava        | Ellis     |\n",
    "| 2017-08-14 | Early      | Logan      | Butler    |\n",
    "| 2017-08-14 | Late       | Logan      | Butler    |\n",
    "| 2017-08-15 | Early      | Logan      | Butler    |\n",
    "| 2017-08-15 | Late       | Logan      | Butler    |\n",
    "| 2017-08-16 | Early      | Logan      | Butler    |\n",
    "| 2017-08-16 | Late       | Logan      | Butler    |\n",
    "+------------+------------+------------+-----------+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<caption>A data.frame: 10 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Shift_date</th><th scope=col>Shift_type</th><th scope=col>First_name</th><th scope=col>Last_name</th></tr>\n",
       "\t<tr><th scope=col>&lt;date&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2017-08-12</td><td>Early</td><td>Logan</td><td>Butler</td></tr>\n",
       "\t<tr><td>2017-08-12</td><td>Late </td><td>Ava  </td><td>Ellis </td></tr>\n",
       "\t<tr><td>2017-08-13</td><td>Early</td><td>Ava  </td><td>Ellis </td></tr>\n",
       "\t<tr><td>2017-08-13</td><td>Late </td><td>Ava  </td><td>Ellis </td></tr>\n",
       "\t<tr><td>2017-08-14</td><td>Early</td><td>Logan</td><td>Butler</td></tr>\n",
       "\t<tr><td>2017-08-14</td><td>Late </td><td>Logan</td><td>Butler</td></tr>\n",
       "\t<tr><td>2017-08-15</td><td>Early</td><td>Logan</td><td>Butler</td></tr>\n",
       "\t<tr><td>2017-08-15</td><td>Late </td><td>Logan</td><td>Butler</td></tr>\n",
       "\t<tr><td>2017-08-16</td><td>Early</td><td>Logan</td><td>Butler</td></tr>\n",
       "\t<tr><td>2017-08-16</td><td>Late </td><td>Logan</td><td>Butler</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 10 × 4\n",
       "\\begin{tabular}{llll}\n",
       " Shift\\_date & Shift\\_type & First\\_name & Last\\_name\\\\\n",
       " <date> & <chr> & <chr> & <chr>\\\\\n",
       "\\hline\n",
       "\t 2017-08-12 & Early & Logan & Butler\\\\\n",
       "\t 2017-08-12 & Late  & Ava   & Ellis \\\\\n",
       "\t 2017-08-13 & Early & Ava   & Ellis \\\\\n",
       "\t 2017-08-13 & Late  & Ava   & Ellis \\\\\n",
       "\t 2017-08-14 & Early & Logan & Butler\\\\\n",
       "\t 2017-08-14 & Late  & Logan & Butler\\\\\n",
       "\t 2017-08-15 & Early & Logan & Butler\\\\\n",
       "\t 2017-08-15 & Late  & Logan & Butler\\\\\n",
       "\t 2017-08-16 & Early & Logan & Butler\\\\\n",
       "\t 2017-08-16 & Late  & Logan & Butler\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 10 × 4\n",
       "\n",
       "| Shift_date &lt;date&gt; | Shift_type &lt;chr&gt; | First_name &lt;chr&gt; | Last_name &lt;chr&gt; |\n",
       "|---|---|---|---|\n",
       "| 2017-08-12 | Early | Logan | Butler |\n",
       "| 2017-08-12 | Late  | Ava   | Ellis  |\n",
       "| 2017-08-13 | Early | Ava   | Ellis  |\n",
       "| 2017-08-13 | Late  | Ava   | Ellis  |\n",
       "| 2017-08-14 | Early | Logan | Butler |\n",
       "| 2017-08-14 | Late  | Logan | Butler |\n",
       "| 2017-08-15 | Early | Logan | Butler |\n",
       "| 2017-08-15 | Late  | Logan | Butler |\n",
       "| 2017-08-16 | Early | Logan | Butler |\n",
       "| 2017-08-16 | Late  | Logan | Butler |\n",
       "\n"
      ],
      "text/plain": [
       "   Shift_date Shift_type First_name Last_name\n",
       "1  2017-08-12 Early      Logan      Butler   \n",
       "2  2017-08-12 Late       Ava        Ellis    \n",
       "3  2017-08-13 Early      Ava        Ellis    \n",
       "4  2017-08-13 Late       Ava        Ellis    \n",
       "5  2017-08-14 Early      Logan      Butler   \n",
       "6  2017-08-14 Late       Logan      Butler   \n",
       "7  2017-08-15 Early      Logan      Butler   \n",
       "8  2017-08-15 Late       Logan      Butler   \n",
       "9  2017-08-16 Early      Logan      Butler   \n",
       "10 2017-08-16 Late       Logan      Butler   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "shift %>% \n",
    "    inner_join(staff, by=c(Manager=\"Staff_code\")) %>%\n",
    "    mutate(Shift_date=as.Date(Shift_date)) %>%\n",
    "    distinct(Shift_date, Shift_type, First_name, Last_name) %>%\n",
    "    arrange(Shift_date, Shift_type)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "dbDisconnect(con)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.0.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
